﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace AscendEast_DataEntry
{
    public partial class Form_State_Edit : Form
    {
        OleDbConnection dbconn;
        OleDbCommand dbcmd = new OleDbCommand();
        String constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\db.mdb;Persist Security Info=False";

        String state_code;
        String state_name;
        String state_code_updated;
        String state_name_updated;
        

        DataAccess dal = new DataAccess();       
        
        public Form_State_Edit()
        {
            InitializeComponent();
            GenerateComboBoxValues_states();
            tb_state_code_state_edit.Enabled = false;
        }

        private void GenerateComboBoxValues_states()
        {
            OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from tbl_states", constr);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
            DataSet ds = new DataSet();
            dAdapter.Fill(ds);
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("state_code", typeof(String));
            dataTable.Columns.Add("state_name", typeof(String));
            dataTable.Rows.Add("null", "Select State");
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                dataTable.Rows.Add(ds.Tables[0].Rows[i][0], ds.Tables[0].Rows[i][1]);
            }

            cb_state_code_edit_search.DisplayMember = dataTable.Columns["state_name"].ToString();
            cb_state_code_edit_search.ValueMember = dataTable.Columns["state_code"].ToString();
            cb_state_code_edit_search.DataSource = dataTable;
        }

        private void btn_search_edit_states_Click(object sender, EventArgs e)
        {
            state_code = cb_state_code_edit_search.SelectedValue.ToString();
            if (cb_state_code_edit_search.SelectedIndex == 0)
                MessageBox.Show("Please select any State");
            else
                PopulateDataGridView(state_code); 
        }

        private void btn_update_state_edit_Click(object sender, EventArgs e)
        {
             if (state_code == null)
                MessageBox.Show("Please select a State");
             else if (state_code.Equals("null"))
                 MessageBox.Show("Please select a State");
             else
             {
                               
                 state_name_updated = tb_state_name_state_edit.Text;                
                 
                 bool duplicate_flag = dal.CheckDuplicate("tbl_states", "state_name", state_name_updated);
                 if (duplicate_flag == true)
                     MessageBox.Show("State Code already exists. Please provide another State");
                 else
                 {
                     String update_state_query = "update tbl_states set state_name = '" + state_name_updated + "' where state_code = '" + state_code + "'";
                     dbconn = new OleDbConnection(constr);
                     dbcmd.Connection = dbconn;
                     dbcmd.CommandType = CommandType.Text;
                     dbcmd.CommandText = update_state_query;
                     dbcmd.Connection.Open();
                     int temp = dbcmd.ExecuteNonQuery();
                     dbcmd.Connection.Close();
                     if (temp > 0)
                         MessageBox.Show("Record Updated Successfully");
                     else
                         MessageBox.Show("Try again");

                     dgv_state_edit.DataSource = null;
                     dgv_state_edit.Rows.Clear();
                     dgv_state_edit.Refresh();
                     
                     PopulateDataGridView(state_code);
                     GenerateComboBoxValues_states();

                     state_code = null;                     
                     state_code_updated = null;
                     state_name_updated = null;
                     tb_state_code_state_edit.Text = null;
                     tb_state_name_state_edit.Text = null;
                 }
             }
        }

        private void PopulateDataGridView(String state_code)
        {
            dgv_state_edit.DataSource = null;
            dgv_state_edit.Rows.Clear();
            dgv_state_edit.Refresh();

            OleDbDataAdapter dAdapter = new OleDbDataAdapter("select * from tbl_states where state_code = '" + state_code + "'", constr);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            DataTable dataTable = new DataTable();
            //DataSet ds = new DataSet();
            dAdapter.Fill(dataTable);
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                dgv_state_edit.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1]);
            }
            tb_state_code_state_edit.Text = dataTable.Rows[0][0].ToString();
            tb_state_name_state_edit.Text = dataTable.Rows[0][1].ToString();
            
        }

        private void btn_cancel_states_edit_Click(object sender, EventArgs e)
        {
            dgv_state_edit.DataSource = null;
            dgv_state_edit.Rows.Clear();
            dgv_state_edit.Refresh();
            cb_state_code_edit_search.SelectedIndex = 0;
            state_code = null;
            state_name = null;
            state_code_updated = null;
            state_name_updated = null;
            tb_state_code_state_edit.Text = null;
            tb_state_name_state_edit.Text = null;
        }

        
    }
}
